﻿using System.Data;
using System.Data.SqlClient;
using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
/// <summary>
/// Dataaccess class for Planes
/// </summary>

namespace DBLayer
{
    public class PlaneDB
    {
        //Variabler initialization
        private SqlConnection con;
        private SqlUtility sqlUtility;
        private SeatDB seatDB;

        public PlaneDB()
        {
            sqlUtility = new SqlUtility();
            seatDB = new SeatDB();
        }
        //Insert plane into database 
        public int InsertPlane(Plane _plane)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "INSERT into Plane (planeNo, totalSeat, planeType) Values (@planeNo, @totalSeat, @planeType)";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@planeNo", _plane.PlaneNo);
            cmd.Parameters.AddWithValue("@totalSeat", _plane.TotalSeat);
            cmd.Parameters.AddWithValue("@planeType", _plane.PlaneType);

            return sqlUtility.ExecuteData(cmd, connect);
        }
        //Find Plane by string (planeNo)
        public Plane FindPlaneByPlaneNo(string _planeNo)
        {
            //bool exists = false;
            Plane retPlane = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Plane WHERE planeNo = @planeNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@planeNo", _planeNo);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            retPlane = BuildPlane(reader);
                        }
                        reader.Close();
                        reader.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retPlane;
        }
        //return a List<Plane> with all the Plane in the database
        public List<Plane> SelectAll()
        {
            List<Plane> planes = new List<Plane>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Plane";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Plane tmpPlane = new Plane();
                            tmpPlane = BuildPlane(reader);
                            planes.Add(tmpPlane);
                        }
                        reader.Close();
                        reader.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (Plane p in planes)
            {
                p.Seats = seatDB.FindSeatsByPlane(p);
            }
            return planes;
        }
        //Update Plane in database by object Plane
        public int UpdatePlane(Plane _plane)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "UPDATE Plane SET planeNo = @planeNo, totalSeat = @totalSeat, planeType = @planeType WHERE planeNo = @planeNo";
            SqlCommand cmd = new SqlCommand(query,connect);
            cmd.Parameters.AddWithValue("planeNo", _plane.PlaneNo);
            cmd.Parameters.AddWithValue("totalSeat", _plane.TotalSeat);
            cmd.Parameters.AddWithValue("planeType", _plane.PlaneType);

            return sqlUtility.ExecuteData(cmd, connect);
        }
        //remove Plane in database by object 
        public int RemovePlane(Plane _plane)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Plane WHERE planeNo = @planeNo";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@planeNo", _plane.PlaneNo);
            return sqlUtility.ExecuteData(cmd,connect);

        }
        //build object Plane from data from database
        private Plane BuildPlane(SqlDataReader _reader)
        {
            Plane temPlane = new Plane();

            temPlane.TotalSeat = (int) _reader["totalSeat"];
            temPlane.PlaneNo = (string) _reader["planeNo"];
            temPlane.PlaneType = (string) _reader["planeType"];

            return temPlane;
        }
    }
}
